package com.eunion.web.admin.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eunion.web.admin.entity.AdminRole;

@Repository
public class AdminRoleRepositoryCustomImpl implements AdminRoleRepositoryCustom {

    @Autowired
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Object[]> groupBySql() {
        // where status = 1
        // final String sql = "select parent_auth_code,count(*) from admin_auth group by parent_auth_code";
        final String sql = "select updated_by,count(*) from admin_role  group by updated_by";
        List<Object[]> list = entityManager.createNativeQuery(sql).getResultList();

        return list;
    }

    @Override
    public List<Object[]> groupByHql() {
        final String hql = "select updatedBy,count(*) from AdminRole group by updatedBy";
        List<Object[]> list = entityManager.createQuery(hql).getResultList();
        return list;
    }

    @Override
    public List<Object[]> groupBySpecification() {
        // 根据地址分组查询，并且学生数量大于3的所有地址
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = builder.createQuery(Object[].class);
        Root<AdminRole> root = query.from(AdminRole.class);
        query.multiselect(root.get("updatedBy"), builder.count(root.get("roleCode"))).groupBy(root.get("updatedBy"));
        //.having(builder.gt(builder.count(root.get("id")), 3));
        return entityManager.createQuery(query).getResultList();
    }
}
